package com.dayu.helper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PostgreSQLHelper {

    /***
     * 数据库链接串（动态）
     */
    public static String DataBaseContentUrl = "jdbc:postgresql://%s";
    /***
     * 驱动类
     */
    public static final String DriverClassName = "org.postgresql.Driver";

    /***
     * 获取数据库库列表
     * @return
     * @throws SQLException
     */
    public static List<String> QueryDataBaseList() throws SQLException {
        DataBaseContentUrl = String.format("jdbc:postgresql://%s/%s", EntryHelper.DataBaseUrl, EntryHelper.DataBaseInit);

        List<String> listDatabase = new ArrayList<>();
        Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT datname FROM pg_database WHERE datistemplate = false;");
        while (resultSet.next()) {
            String dbName = resultSet.getString("datname");
            listDatabase.add(dbName);
        }
        return listDatabase;
    }

    /***
     * 获取库下所有表
     * 格式：表名 - 表描述
     * @return
     * @throws SQLException
     */
    public static List<String> QueryAllTable() throws SQLException {
        DataBaseContentUrl = String.format("jdbc:postgresql://%s/%s", EntryHelper.DataBaseUrl, EntryHelper.DataBaseSele);

        List<String> listTable = new ArrayList<>();
        Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
                "SELECT " +
                        "   c.relname AS table_name, " +
                        "   COALESCE(pg_catalog.obj_description(c.oid, 'pg_class'), '') AS table_comment," +
                        "   n.nspname" +
                        "   FROM  pg_catalog.pg_class c " +
                        "   JOIN  pg_catalog.pg_namespace n ON n.oid = c.relnamespace" +
                        "   WHERE c.relkind = 'r' " +
                        "   AND n.nspname NOT IN ('pg_catalog', 'information_schema') ");
        while (resultSet.next()) {
            String tableName = resultSet.getString("table_name");
            String tableComment = resultSet.getString("table_comment");
            String nspname = resultSet.getString("nspname");
            listTable.add(tableName + " - " + (tableComment != null ? tableComment : "") + "("+nspname+")");
        }
        return listTable;
    }

    /**
     * 测试数据库连接
     * @return
     * @throws SQLException
     */
    public static boolean verifyConnection() throws SQLException {
        try {
            DataBaseContentUrl = String.format("jdbc:postgresql://%s/%s", EntryHelper.DataBaseUrl, EntryHelper.DataBaseInit);
            Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
            if (connection != null) {
                connection.close();
                return true;
            }
        } catch (SQLException e) {
            throw e;
        }
        return false;
    }
}
